package com.glxy.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.ResourceBundle;

public class DBUtils {
    /*
    Dbutils主要有三个核心类

    QueryRunner: 提供对sql语句操作的API.用于对数据库增删改查
    ResultSetHandler接口:用于定义select操作后，怎样封装结果集，封装成javaBean
    DbUtils:它就是一个工具类,定义了关闭资源与事务处理的方法
    */


    private static DBUtils instance = null;
    private DBUtils() {}
    public static DBUtils getInstance() {
        if (instance == null){
            instance = new DBUtils();
        }
        return instance;
    }

    private static final ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
    private static final String driver = bundle.getString("driver");
    private static final String url = bundle.getString("url");
    private static final String user = bundle.getString("user");
    private static final String password = bundle.getString("password");

    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    private static void close(Connection conn, PreparedStatement ps, ResultSet res) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (res != null) {
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //Read:
    public <K> ArrayList<K> executeQuery(String sql, ArrayList<Object> params, Class<K> clazz) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet res = null;
        ResultSetMetaData meta;

        ArrayList<K> result = new ArrayList<>();

        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);

            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i + 1, params.get(i));
                }
            }
            res = ps.executeQuery();
            meta = res.getMetaData();
            int colCount = meta.getColumnCount();

            while (res.next()) {
                K row = clazz.getDeclaredConstructor().newInstance();
                for (int i = 0; i < colCount; i++) {
                    String colName = meta.getColumnName(i+1);
                    Object colVal = res.getObject(i+1);
                    Field field = clazz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(row, colVal);
                }
                result.add(row);
            }

        } catch (SQLException | InvocationTargetException | InstantiationException |
                 IllegalAccessException | NoSuchMethodException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.close(conn, ps, res);
        }

        return result;
    }

    //Create Update Delete:
    public Integer execute(String sql, ArrayList<Object> params) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;

        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);

            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i + 1, params.get(i));
                }
            }

            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, ps, null);
        }

        return count;
    }
}
